package Interface.Producao.Gloss;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import sft.BancodeDados.CriaConexao;
import sft.Logica.ProducaoBusca;

public class QueryGlos {
    private static Connection conexao;

    public QueryGlos() throws SQLException {
        conexao = new CriaConexao().getConnection();
    }
    
    public List<ProducaoBusca> getList() throws SQLException{
        
        String sql = "select  g.glos_os_numos, s.usua_nm_nmusua , n.ncon_nm_nmncon , c.conv_nm_nmconv, "
                + "                u.unid_nm_nmunid, g.glos_dt_dtregis ,g.glos_vl_vlos, "
                + "                g.glos_ob_obsglos, g.glos_st_status "
                + "from sft_glos g join sft_usua s on(g.fk_usua = s.usua_id_idusua) "
                + "                join sft_ncon n on(g.fk_ncon = n.ncon_id_idncon) "
                + "                join sft_unid u on(g.fk_unid = u.unid_id_idunid) "
                + "                join sft_conv c on(g.fk_conv = c.conv_id_idconv) "
                + "                Order by g.glos_id_idglos desc";
        
        PreparedStatement stmt = conexao.prepareStatement(sql);
        
        return setSTMT(stmt);
    }
    
    public List<ProducaoBusca> getList(java.util.Date data) throws SQLException{
        
        String sql = "select  g.glos_os_numos, s.usua_nm_nmusua , n.ncon_nm_nmncon , c.conv_nm_nmconv, " +
"                                u.unid_nm_nmunid, g.glos_dt_dtregis ,g.glos_vl_vlos, " +
"                                g.glos_ob_obsglos, g.glos_st_status " +
"                from sft_glos g join sft_usua s on(g.fk_usua = s.usua_id_idusua) " +
"                                join sft_ncon n on(g.fk_ncon = n.ncon_id_idncon) " +
"                                join sft_unid u on(g.fk_unid = u.unid_id_idunid) " +
"                                join sft_conv c on(g.fk_conv = c.conv_id_idconv) " +
"                                WHERE g.glos_dt_dtregis >= ? " +
"                                Order by g.glos_id_idglos desc";
        
        
        java.sql.Date dat = new java.sql.Date(data.getTime());
        SimpleDateFormat date = new SimpleDateFormat("YYYY-MM");
        
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setString(1,date.format(dat));
        
        return setSTMT(stmt);
    }
    
    public List<ProducaoBusca> getList(String os) throws SQLException{
        
        String sql = "select  g.glos_os_numos, s.usua_nm_nmusua , n.ncon_nm_nmncon , c.conv_nm_nmconv, "
                + "                u.unid_nm_nmunid, g.glos_dt_dtregis ,g.glos_vl_vlos, "
                + "                g.glos_ob_obsglos, g.glos_st_status "
                + "from sft_glos g join sft_usua s on(g.fk_usua = s.usua_id_idusua) "
                + "                join sft_ncon n on(g.fk_ncon = n.ncon_id_idncon) "
                + "                join sft_unid u on(g.fk_unid = u.unid_id_idunid) "
                + "                join sft_conv c on(g.fk_conv = c.conv_id_idconv) "
                + "                where g.glos_os_numos like ?"
                + "                Order by g.glos_id_idglos desc";
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setString(1, "%" + os + "%");
        
        return setSTMT(stmt);
    }

    private List<ProducaoBusca> setSTMT(PreparedStatement stmt) throws SQLException {
        ResultSet rs = stmt.executeQuery();
        
        List<ProducaoBusca> minhaLista = new ArrayList<>();
        while (rs.next()) {      
            
            ProducaoBusca p = new ProducaoBusca();
            p.setOS(rs.getString(1));
            p.setUsua(rs.getString(2));
            p.setnCon(rs.getString(3));
            p.setConv(rs.getString(4));
            p.setUnid(rs.getString(5));
            p.setData(rs.getDate(6));
            p.setValor(rs.getDouble(7));         
            p.setObs(rs.getString(8));
            p.setStatus(rs.getString(9));
            minhaLista.add(p);
        }
        return minhaLista;
    }
    
}
